package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.AdPublishConditionRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.ConnectionObject;
import com.ndp.fb.rdb.model.EntrustAccount;
import com.ndp.fb.rdb.model.adpublishcondition.*;
import com.ndp.fb.util.SQLUtil;
import com.ndp.fb.util.StringUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by Evan on 2015/3/23.
 */
@Service
public class AdPublishConditionRdbServiceImpl extends DaoTemplate implements AdPublishConditionRdbService {

    @Override
    public Long saveAdPublishCondition(AdPublishCondition obj) {
        return saveResultId(obj);
    }

    @Override
    public void saveAdPublishConditionDetail(final AdPublishConditionDetail detail) {
        saveResultId(detail);
    }


    @Override
    public void updateTimeById(Long id, Date updateTime, Long updateUtcTime) {
        String sql = "UPDATE ad_publish_condition SET update_time = ?, update_utc_time = ?  WHERE 1=1 AND id = ?";
        Object[] param = new Object[]{updateTime, updateUtcTime, id};
        getJdbcTemplate().update(sql, param);
    }

    @Override
    public List<AdPublishConditionResult> findAdPublishConditionWithPager(String userId, String key4Name, Long orgId) {
        if (StringUtil.isEmpty(key4Name)) {
            key4Name=""; // 初始化 关键字，后边就不用判断了
        }
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT DISTINCT ");
        sql.append("  t2.id, ");
        sql.append("  t2.user_id, ");
        sql.append("   t1.fb_connection_object_id connection_object_id, ");
        sql.append("   case when t2.STATUS is null then 0 else t2. STATUS end as status, ");
        sql.append("   t1.id product_configuration_id,");
        sql.append("   t1.platform, t2.product_type, ");
        sql.append("   case t1.platform when  'itunes' then 1 when  'google_play' then 2 when 'itunes_ipad' then 3 ELSE 1   end as product_configuration_type, ");
        sql.append("   t1.NAME connection_object_name, ");
        sql.append("  case when t2.update_utc_time is null then t2.create_utc_time else t2.update_utc_time end as operation_time, ");
        sql.append("  case when t2.operation_status is null then 0 else t2.operation_status end as operation_status ");
        sql.append("FROM ");
        sql.append("   ( ");
        sql.append("       SELECT DISTINCT ");
        sql.append("           pro.id, ");
        sql.append("           pro.fb_connection_object_id, ");
        sql.append("           connt.NAME, ");
        sql.append("           pro.platform, ");
        sql.append("      fbuser.timezone ");
        sql.append("       FROM ");
        sql.append("           fb_user fbuser, ");
        sql.append("           fb_userpermissions_adaccounts fua, ");
        sql.append("           entrust_account ent, ");
        sql.append("           entrust_account_connection_objects entcon, ");
        sql.append("           connection_object connt, ");
        sql.append("           product_configuration pro ");
        sql.append("       WHERE fbuser.fb_user_id = fua.fb_user_id ");
        sql.append("       AND fua.fb_account_id = ent.fb_account_id ");
        sql.append("       AND fua.role IN ('GENERAL_USER','ADMIN') ");
        sql.append("       AND fua.status = 'ACTIVE' ");
        sql.append("       AND entcon.fb_account_id = ent.fb_account_id     ");
        sql.append("       AND entcon.fb_connection_object_id = connt.fb_connection_object_id ");
        sql.append("       AND connt.fb_connection_object_id = pro.fb_connection_object_id ");
        sql.append("       AND connt.type = 2 ");
//        if (StringUtil.isNotEmpty(key4Name)) {
            sql.append("     and connt.name like ?");
//        }
        sql.append("       AND pro.platform <> 'fb_canvas' ");
        sql.append("       AND fbuser.user_id = ?");
        sql.append("   ) t1 "); // t1 用户全部有效的应用。
        sql.append("LEFT JOIN (SELECT DISTINCT ");
        sql.append("ap.id, ");
        sql.append("ap.user_id, ");
        sql.append("ap.platform, ");
        sql.append("ap.product_type, ");
        sql.append("ap.connection_object_id, ");
        sql.append("ap.connection_object_name, ");
        sql.append("ap.product_configuration_id, ");
        sql.append("ap. STATUS, ");
        sql.append("ap.create_utc_time, ");
        sql.append("ap.operation_status, ");
        sql.append("ap.update_utc_time ");
        sql.append("FROM ");
        sql.append("ad_publish_condition ap, ");
        sql.append("fb_user fbuser, ");
        sql.append("fb_userpermissions_adaccounts fua, ");
        sql.append("entrust_account_connection_objects entcon ");
        sql.append("WHERE ");
        sql.append("fbuser.fb_user_id = fua.fb_user_id ");
        sql.append("AND fua.fb_account_id = entcon.fb_account_id ");
        sql.append("AND fua.role IN ('GENERAL_USER', 'ADMIN') ");
        sql.append("AND fua. STATUS = 'ACTIVE' ");
        sql.append("AND ap.`connection_object_id` = entcon.`fb_connection_object_id` ");
        sql.append("AND fbuser.user_id = ?  ");
        sql.append("AND ap.user_id = ? ");
        sql.append(") t2 ON ( ");  // t2应用的投放控制。不是所有的应用都控制了（界面上‘修改’和‘设置’的区别），所以用left join，查出所有应用和控制情况。
        sql.append("   t1.fb_connection_object_id = t2.connection_object_id ");
        sql.append("   AND t1.id = t2.product_configuration_id ");
//        sql.append("   AND t2.user_id = ? ");
        sql.append(") ");
        sql.append(" UNION ALL  "); // union 外链广告
        sql.append("SELECT  ");
        sql.append("  p.id, ");
        sql.append("  p.user_id, ");
        sql.append("   p.connection_object_id connection_object_id, ");
        sql.append("   case when p.status is null then 0 else p.status end as status, ");
        sql.append("   p.product_configuration_id,");
        sql.append("   p.platform, p.product_type, ");
        sql.append("   case p.platform when  'itunes' then 1 when  'google_play' then 2 when 'itunes_ipad' then 3 ELSE 1   end as product_configuration_type, ");
        sql.append("   p.connection_object_name, ");
        sql.append("  case when p.update_utc_time is null then p.create_utc_time else p.update_utc_time end as operation_time, ");
        sql.append("  case when p.operation_status is null then 0 else p.operation_status end as operation_status ");
        sql.append("FROM ad_publish_condition p, product_manage pm WHERE p.connection_object_id=pm.product_id AND p.platform = pm.product_platform AND pm.is_delete = 0 AND p.product_type='outlink' AND pm.org_id=? AND p.user_id = ?");
//        if (StringUtil.isNotEmpty(key4Name)) {
            sql.append("AND p.connection_object_name like ?");
//        }
        sql.append("ORDER BY status desc , connection_object_name");
        return getJdbcTemplate().query(sql.toString(), new Object[]{"%" + key4Name + "%", userId, userId, userId, orgId, userId, "%" + key4Name + "%"}, DefaultRowMapper.autoRowMapper(AdPublishConditionResult.class));
//        if (StringUtil.isNotEmpty(key4Name)) {
//        return getJdbcTemplate().query(sql.toString(), new Object[]{"%" + key4Name + "%", userId, userId, userId, orgId, "%" + key4Name + "%"}, DefaultRowMapper.autoRowMapper(AdPublishConditionResult.class));
//        } else {
//            return getJdbcTemplate().query(sql.toString(), new Object[]{userId, userId, userId, orgId}, DefaultRowMapper.autoRowMapper(AdPublishConditionResult.class));
//        }
    }

    @Override
    public AdPublishCondition findAdPublishCondition(String userId, Long connObjId, Long prodCfgId) {
        String sql = "SELECT * FROM ad_publish_condition WHERE user_id =? AND connection_object_id = ? AND product_configuration_id = ?";
        return findOne(sql, new Object[]{userId, connObjId, prodCfgId}, DefaultRowMapper.autoRowMapper(AdPublishCondition.class));
    }

    @Override
    public void updateStatus(String userId, Long id, Boolean status) {
        String sql = "UPDATE ad_publish_condition SET status = ? WHERE 1=1 AND id = ? AND user_id=? ";
        Object[] param = new Object[]{status, id, userId};
        getJdbcTemplate().update(sql, param);
    }

    @Override
    public AdPublishCondition findById(Long id) {
        return findOneByProperty("id", id, DefaultRowMapper.autoRowMapper(AdPublishCondition.class));
    }



    @Override
    public List<AdPublishConditionAccount> findAdPublishConditionAccount(Long conditionId,Long fbUserId) {
        String sql = "SELECT DISTINCT"
                + " fb_userpermissions_adaccounts.fb_account_id,"
                + " ad_publish_condition_detail_account.id,"
                + " ad_publish_condition_detail_account.condition_id,"
                + " ad_publish_condition_detail_account.display_name,"
                + " ad_publish_condition_detail_account.is_support_cpa,"
                + " fb_userpermissions_adaccounts.role"
                + " FROM"
                + " ad_publish_condition_detail_account"
                + " INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = ad_publish_condition_detail_account.fb_account_id"
                + " WHERE fb_userpermissions_adaccounts.role IN ('GENERAL_USER','ADMIN') AND fb_userpermissions_adaccounts.status = 'ACTIVE' AND fb_userpermissions_adaccounts.fb_user_id = ? AND ad_publish_condition_detail_account.condition_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{fbUserId,conditionId}, DefaultRowMapper.autoRowMapper(AdPublishConditionAccount.class));
    }

    @Override
    public List<EntrustAccount> findAllowedAccounts(String userId, Long connObjId, Long prodCfgId) {
        String sql = "SELECT DISTINCT"
                + " entrust_account.fb_account_id,"
                + " entrust_account.id,"
                + " entrust_account.`name`,"
                + " entrust_account.email,"
                + " entrust_account.account_status,"
                + " entrust_account.time_zone,"
                + " entrust_account.daily_spend_limit,"
                + " entrust_account.balance,"
                + " entrust_account.remark,"
                + " entrust_account.custom_audience_tos,"
                + " entrust_account.create_time,"
                + " entrust_account.update_time,"
                + " entrust_account.is_delete,"
                + " entrust_account.is_optimize,"
                + " ad_publish_condition.user_id,"
                + " ad_publish_condition_detail_account.condition_id,"
                + " fb_userpermissions_adaccounts.fb_account_id,"
                + " fb_user.fb_user_id"
                + " FROM"
                + " ad_publish_condition_detail_account"
                + " INNER JOIN fb_userpermissions_adaccounts ON fb_userpermissions_adaccounts.fb_account_id = ad_publish_condition_detail_account.fb_account_id"
                + " INNER JOIN ad_publish_condition ON ad_publish_condition.id = ad_publish_condition_detail_account.condition_id"
                + " INNER JOIN entrust_account ON fb_userpermissions_adaccounts.fb_account_id = entrust_account.fb_account_id"
                + " INNER JOIN fb_user ON fb_user.fb_user_id = fb_userpermissions_adaccounts.fb_user_id AND fb_user.user_id = ad_publish_condition.user_id"
                + " WHERE fb_userpermissions_adaccounts.role IN ('GENERAL_USER','ADMIN')  AND fb_userpermissions_adaccounts.status = 'ACTIVE' AND fb_user.user_id = ? and ad_publish_condition.connection_object_id = ? AND ad_publish_condition.product_configuration_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{userId, connObjId, prodCfgId}, DefaultRowMapper.autoRowMapper(EntrustAccount.class));
    }

    @Override
    public List<RegionAndPrice> findRegionAndPrice(Long conditionId) {
        String sql = "SELECT * FROM ad_publish_condition_detail_regionandprice WHERE 1=1 AND condition_id = ? ";
        return getJdbcTemplate().query(sql, new Object[]{conditionId}, DefaultRowMapper.autoRowMapper(RegionAndPrice.class));
    }


    @Override
    public List<RegionAndPriceExt> findAllRegionAndPricesByAppIds(String ticket, List<String> strings) {

        int size = strings.size();
        String sql = "SELECT DISTINCT\n" +
                "	adpc.* ,adp.connection_object_id,adp.connection_object_name\n" +
                "FROM\n" +
                "	fb_user fbuser,\n" +
                "	fb_userpermissions_adaccounts fua,\n" +
                "	entrust_account_connection_objects entcon,\n" +
                "	ad_publish_condition adp,\n" +
                "	ad_publish_condition_detail_regionandprice adpc\n" +
                "WHERE\n" +
                "	fbuser.fb_user_id = fua.fb_user_id\n" +
                "AND fua.fb_account_id = entcon.fb_account_id\n" +
                "AND entcon.fb_connection_object_id = adp.connection_object_id\n" +
                "AND adp.id = adpc.condition_id\n" +
                "AND adp.connection_object_id in(" + SQLUtil.appendPlaceholder(size) + ")\n" +
                "AND fbuser.user_id = ?\n"+
                "AND adp.user_id = ?";


        String[] strings2 = new String[size + 2];

        for (int i = 0; i < size; i++) {
            strings2[i] = strings.get(i);
        }
        strings2[size] = ticket;
        strings2[size + 1] = ticket;
        return getJdbcTemplate().query(sql,strings2, DefaultRowMapper.autoRowMapper(RegionAndPriceExt.class));
    }

    @Override
    public void updateDetail(AdPublishConditionDetail detail) {
        StringBuffer sb = new StringBuffer();
        sb.append("UPDATE ad_publish_condition_detail SET ");

        List<String> udpateSql = new ArrayList<>();
        List udpateParams = new ArrayList();
        if (detail.getPageId() != null) {
            udpateSql.add("page_id = ?");
            udpateParams.add(detail.getPageId());
        }
        if (detail.getPlatform() != null) {
            udpateSql.add("platform = ?");
            udpateParams.add(detail.getPlatform());
        }
        if (detail.getAge() != null) {
            udpateSql.add("age = ?");
            udpateParams.add(detail.getAge());
        }
        if (detail.getSex() != 0) {
            udpateSql.add("sex = ?");
            udpateParams.add(detail.getSex());
        }
        if (detail.getPriceLimit() != null) {
            udpateSql.add("price_limit = ?");
            udpateParams.add(detail.getPriceLimit());
        }
        if (detail.getConnObjSimpleName() != null) {
            udpateSql.add("conn_obj_simple_name = ?");
            udpateParams.add(detail.getConnObjSimpleName());
        }
        for (int i1 = 0; i1 < udpateSql.size(); i1++) {
            if (i1 + 1 == udpateSql.size()) {
                sb.append(udpateSql.get(i1));
            } else {
                sb.append(udpateSql.get(i1)).append(" , ");
            }
        }
        sb.append(" WHERE 1=1 AND condition_id = ? ");
        udpateParams.add(detail.getConditionId());
        getJdbcTemplate().update(sb.toString(), udpateParams.toArray());
    }

    @Override
    public void updateRegionAndPrice(final List<RegionAndPrice> regionAndPrices, final Long conditionId) {

        String delSql = "DELETE FROM ad_publish_condition_detail_regionandprice WHERE condition_id=?";
        getJdbcTemplate().update(delSql, new Object[]{conditionId});
        batchSaveRegionAndPrice(regionAndPrices,conditionId);
    }


    @Override
    public void batchSaveRegionAndPrice(final List<RegionAndPrice> regionAndPrices, final Long conditionId) {
        for (RegionAndPrice regionAndPrice : regionAndPrices) {
            regionAndPrice.setConditionId(conditionId);
        }
        batchSaveResultId(regionAndPrices);
    }


    @Override
    public void updateAccount(final List<AdPublishConditionAccount> accounts, final Long conditionId) {
        String delSql = "DELETE FROM ad_publish_condition_detail_account WHERE condition_id=?";
        getJdbcTemplate().update(delSql, new Object[]{conditionId});
        batchSaveAccount(accounts,conditionId);
    }

    @Override
    public void batchSaveAccount(final List<AdPublishConditionAccount> accounts, final Long conditionId) {
        for (AdPublishConditionAccount account : accounts) {
            account.setConditionId(conditionId);
        }
        batchSaveResultId(accounts);
    }


    @Override
    public List<ConnectionObject> findByTypeAndUserId(int type, String userId) {

        String sql = "SELECT * " +
                " FROM (SELECT DISTINCT t1.id AS id, t1.user_id, t1.fb_connection_object_id, t1.type, t1.name " +
                " , t1.native_app_store_ids, t1.native_app_targeting_ids, t1.picture, t1.supported_platforms, t1.object_store_urls " +
                " , t1.url, t1.logo_url, t1.icon_url, CASE WHEN t2.STATUS IS NULL THEN 0 ELSE t2.STATUS END AS status " +
                " FROM (SELECT DISTINCT connt.id, pro.id AS pro_id, fbuser.user_id, connt.fb_connection_object_id, connt.type " +
                " , connt.`name`, connt.native_app_store_ids, connt.native_app_targeting_ids, connt.picture, connt.supported_platforms " +
                " , connt.object_store_urls, connt.url, connt.logo_url, connt.icon_url " +
                " FROM fb_user fbuser, fb_userpermissions_adaccounts fua, entrust_account ent, entrust_account_connection_objects entcon, connection_object connt, product_configuration pro " +
                " WHERE fbuser.fb_user_id = fua.fb_user_id " +
                " AND fua.fb_account_id = ent.fb_account_id " +
                " AND fua.role IN ('GENERAL_USER', 'ADMIN') " +
                " AND fua.status = 'ACTIVE' " +
                " AND entcon.fb_account_id = ent.fb_account_id " +
                " AND entcon.fb_connection_object_id = connt.fb_connection_object_id " +
                " AND connt.fb_connection_object_id = pro.fb_connection_object_id " +
                " AND connt.type = ? " +
                " AND pro.platform <> 'fb_canvas' " +
                " AND fbuser.user_id = ? " +
                " ) t1 LEFT JOIN ad_publish_condition t2 ON t1.fb_connection_object_id = t2.connection_object_id " +
                " AND t1.pro_id = t2.product_configuration_id " +
                " AND t2.user_id = ?  " +
                " ORDER BY status DESC, name " +
                " ) t3 " +
                "WHERE status = 1";

        return getJdbcTemplate().query(sql.toString(), new Object[]{type, userId, userId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }


    @Override
    public List<AdPublishCondition> findOpenedOutlinkByUserIdAndOrgId(String userId, Long orgId)
    {
        String sql = "SELECT p.* FROM ad_publish_condition p, product_manage pm WHERE p.connection_object_id=pm.product_id AND p.platform = pm.product_platform AND pm.is_delete = 0 AND p.product_type='outlink' AND p.status=1 AND p.user_id = ? AND pm.org_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{userId, orgId}, DefaultRowMapper.autoRowMapper(AdPublishCondition.class));
    }

    @Override
    public List<AdPublishCondition> findAll() {
        String sql = "select * from ad_publish_condition";
        return getJdbcTemplate().query(sql,DefaultRowMapper.autoRowMapper(AdPublishCondition.class));
    }

    @Override
    public List<AdPublishConditionAccount> findAdPublishConditionAccountByConditionId(Long conditionId) {
        String sql = "select * from ad_publish_condition_detail_account where condition_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{conditionId},DefaultRowMapper.autoRowMapper(AdPublishConditionAccount.class));
    }

	@Override
	public AdPublishConditionDetail findDetailByConnObjIdPlatForm(String userId, Long connObjId, String platform) {
		String sql = "SELECT * FROM ad_publish_condition_detail WHERE condition_id IN (SELECT id FROM ad_publish_condition t WHERE user_id =? AND connection_object_id = ? AND platform = ?)";
	    return findOne(sql, new Object[]{userId, connObjId, platform}, DefaultRowMapper.autoRowMapper(AdPublishConditionDetail.class));
	}

	@Override
	public AdPublishCondition findAdPublishCondition(String userId, Long connObjId, String platform) {
		 String sql = "SELECT * FROM ad_publish_condition WHERE user_id =? AND connection_object_id = ? AND platform = ?";
	     return findOne(sql, new Object[]{userId, connObjId, platform}, DefaultRowMapper.autoRowMapper(AdPublishCondition.class));
	}

    @Override
    public List<AdPublishCondition> findByIds( List<Long> ids) {
        String q = "";
        for (int i = 0; i < ids.size(); i++){
            if (i > 0 ){
                q += ",";
            }
            q += "?";
        }
        String sql = "select * from ad_publish_condition where id in (" + q + ")";
        return getJdbcTemplate().query(sql, ids.toArray(), DefaultRowMapper.autoRowMapper(AdPublishCondition.class));
    }

    @Override
    public List<UserConnectionPage> findPageIdsByUserId(String userId) {
        String sql="SELECT d.condition_id,c.user_id,c.connection_object_id,d.page_id,c.platform " +
                "FROM ad_publish_condition c,ad_publish_condition_detail d " +
                "WHERE d.condition_id = c.id and c.`status` = 1  and c.user_id= ?";
        return  getJdbcTemplate().query(sql, new Object[]{userId}, DefaultRowMapper.autoRowMapper(UserConnectionPage.class));
    }
}
